<?php
namespace app\index\model;

use app\common\model\RemoteModel;
use think\Config;
use think\Db;

class LRegister extends RemoteModel
{

    protected $connection = 'mysql://root:JB6MUn64xICo@rm-bp1h205f36fv8pecto.mysql.rds.aliyuncs.com:3306/dragon#utf8';
    protected $url = "http://115.239.196.59:19998/?sid=144169&mid=20005&serverid=1&cid=1&tablename=l_register";
    /**
     * 重写父类初始化方法
     *
     * @return   [type]
     */
    protected function initialize()
    {
        parent::initialize();
        
    }
    /**
     * 获取用户生命周期价值
     * 
     * @param    [string] $regDate [注册日期]
     * @param    [string] $calcDate [计算日期]
     * @return   [int]
     */
    public function getLTV($regDate, $calcDate)
    {

        //计算截止时间
        //
        $calcDate = date("Y-m-d", (strtotime($calcDate) + 3600 * 24 ));

        //注册用户角色ID数组
        //
        $reg_ids = $this->getRegCid($regDate);
        $str_ids = join(",", $reg_ids);

        //区间统计
        //
        $sql = "SELECT SUM(`amount`) AS `amount` FROM `verify` WHERE `amount` > 0 AND UNIX_TIMESTAMP(`create_time`) >= UNIX_TIMESTAMP('{$regDate}') AND UNIX_TIMESTAMP(`create_time`) < UNIX_TIMESTAMP('{$calcDate}') AND `character_id` IN ({$str_ids}) LIMIT 1";

        $res     = Db::query($sql);
        $ret_num = $res[0]['amount'];

        return $ret_num;
    }
    /**
     * 注册人数（按日期）
     *
     * 某注册日期的新注册角色ID人数
     *
     * @param    string $date 计算日期
     * @return   int
     */
    public function getRegTotal($date)
    {
        try {
            $res = Db::query("select count(*) as count from l_register where DATEDIFF(`createtime`,'{$date}') = 0 limit 1");
            $rtn = $res[0]['count'];

            return $rtn;

        } catch (\Exception $e) {
            throw new \think\Exception('异常消息:' . $e->getMessage());
        }
    }

    /**
     * 注册总人数（按日期）
     *
     * 截止某注册日期的角色ID总人数
     *
     * @param    string $date 计算日期
     * @return   int
     */
    public function getRegGrandTotal($date)
    {
        try {
            $res = Db::query("select count(*) as count from l_register where DATEDIFF(`createtime`,'{$date}') <= 0 limit 1");
            $rtn = $res[0]['count'];

            return $rtn;

        } catch (\Exception $e) {
            throw new \think\Exception('异常消息:' . $e->getMessage());
        }
    }

    /**
     * 注册日期ID查询
     *
     * 某注册日期所有用户的角色ID
     *
     * @param    string $date 计算日期
     * @return   array 角色ID数组
     */
    public function getRegCid($date)
    {
        try {
            //返回数组
            $rtn = [];
            $res = Db::query("select `characterid` from l_register where DATEDIFF(`createtime`,'{$date}') = 0");
            $rtn = array_column($res, 'characterid');

            return $rtn;

        } catch (\Exception $e) {
            throw new \think\Exception('异常消息:' . $e->getMessage());
        }
    }
}
